package intranet.models;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class MyGroups {

	Connection DBConnection=null;
	public MyGroups(){
		DatabaseModel DBModel=new DatabaseModel();
		DBConnection=DBModel.GetConnection();
	}
	
	/* SELECT MY GROUPS */
	public ResultSet SelectMyGroups(int user_id){
	ResultSet result=null;
	try{
		Statement ConStatement=DBConnection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
		String Sql="SELECT M.GROUP_ID,G.GROUP_NAME,U.USER_NAME FROM (GROUP_MEMBERS M INNER JOIN USER_DEFINED_GROUPS G ON G.GROUP_ID=M.GROUP_ID) INNER JOIN USER_ACCOUNTS U ON U.USER_ID=G.GROUP_OWNER WHERE M.USER_ID="+user_id;
		result=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
	}
	
	/* SELECT MY GROUPS */
	public void DeleteMyGroups(int group_id){
	ResultSet result=null;
	ResultSet result1=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="DELETE FROM GROUP_MEMBERS WHERE GROUP_ID="+group_id;
		result=ConStatement.executeQuery(Sql);
		
		Statement ConStatement1=DBConnection.createStatement();
		String Sql1="DELETE FROM USER_DEFINED_GROUPS WHERE GROUP_ID="+group_id;
		result1=ConStatement1.executeQuery(Sql1);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	}
	
	/* Verify Group Owner */
	public boolean isOwner(int userid,int group_id){
		boolean result=false;
		int i=0;
		ResultSet rst=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT GROUP_ID FROM USER_DEFINED_GROUPS WHERE GROUP_OWNER="+userid+" AND GROUP_ID="+group_id+"";
			rst=ConStatement.executeQuery(Sql);
			while(rst.next()){
				i++;
			}
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		result=(i==0)?false:true;
		return result;
	}
	
	/* VERIFY IF EXISTING MEMBER */
	public boolean isExistingMember(int userid,String group_id){
		boolean result=false;
		int count=0;
		ResultSet rst=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT * FROM GROUP_MEMBERS WHERE USER_ID="+userid+" AND GROUP_ID="+group_id;
			rst=ConStatement.executeQuery(Sql);
			while(rst.next()){
				count++;
			}
			result=(count>0)?true:false;
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	
	/* VERIFY IF EXISTING MEMBER */
	public boolean isExistingGroupName(String group_name){
		boolean result=false;
		int count=0;
		ResultSet rst=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT * FROM USER_DEFINED_GROUPS WHERE GROUP_NAME='"+group_name+"'";
			rst=ConStatement.executeQuery(Sql);
			while(rst.next()){
				count++;
			}
			result=(count>0)?true:false;
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	
	/* SELECT GROUP OWNER */
	public ResultSet SelectGroupOwner(int group_id){
	ResultSet result=null;
	try{
		Statement ConStatement=DBConnection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
		String Sql="SELECT G.GROUP_OWNER,G.GROUP_NAME,U.USER_NAME FROM USER_DEFINED_GROUPS G INNER JOIN USER_ACCOUNTS U ON U.USER_ID=G.GROUP_OWNER WHERE GROUP_ID="+group_id;
		result=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
	}
	
	/* SELECT GROUP MEMBERS */
	public ResultSet SelectMembers(int group_id){
	ResultSet result=null;
	try{
		Statement ConStatement=DBConnection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
		String Sql="SELECT M.USER_ID,U.USER_NAME FROM GROUP_MEMBERS M INNER JOIN USER_ACCOUNTS U ON M.USER_ID=U.USER_ID WHERE M.GROUP_ID="+group_id;
		result=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
	}
	/* Add Member to Group */
	public void AddMember(int group_id,int user_id ){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="INSERT INTO GROUP_MEMBERS (GROUP_ID,USER_ID) VALUES("+group_id+","+user_id+")";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
	}
	
	/* Delete Member */
	public void DeleteMember(int group_id,int user_id ){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="DELETE FROM GROUP_MEMBERS WHERE GROUP_ID="+group_id+" AND USER_ID="+user_id;
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
	}
	
	public ResultSet SelectGroup(String LogName,String id_delete,String id_edit){
		ResultSet result=null;
		String Sql="";
		int id=0;
		if(id_delete!=null)
		id=Integer.parseInt(id_delete);
		if(id_edit!=null)
			id=Integer.parseInt(id_edit);
		try{
			Statement ConStatement=DBConnection.createStatement();		
			if(id_delete!=null){
				String del_id="DELETE FROM USER_DEFINED_GROUPS WHERE GROUP_ID="+id+"";	
				ConStatement.executeUpdate(del_id);
				}
			if(id_edit!=null){
				String edit_id="SELECT UDG.GROUP_NAME,UDG.GROUP_ID,GM.USER_NAME FROM USER_DEFINED_GROUPS UDG INNER JOIN GROUP_MEMBERS GM ON UDG.GROUP_ID=GM.GROUP_ID WHERE UDG.GROUP_ID="+id+"";	
				return result=ConStatement.executeQuery(edit_id);
				}
			Sql="SELECT USER_DEFINED_GROUPS.GROUP_ID,USER_DEFINED_GROUPS.GROUP_NAME FROM USER_DEFINED_GROUPS";
				result=ConStatement.executeQuery(Sql);
		   }catch(Exception e){e.getLocalizedMessage();}
	return result;
	}
	
	
	public String SelectUser(){
		ResultSet result=null;
		//String strx="da.born"+'"';
		String strx="";
		int i=0;
		String Sql="";
		try{
			Statement ConStatement=DBConnection.createStatement();
				Sql="SELECT USER_ID,USER_NAME FROM USER_ACCOUNTS";
				result=ConStatement.executeQuery(Sql);
				
				while(result.next())
					if(i==0){
						strx+=result.getString("USER_NAME")+'"'+","+'"';
					}else{
						strx+='"'+result.getString("USER_NAME")+'"'+",";
				    }
				
		   }catch(Exception e){e.getLocalizedMessage();}
	return strx;
	}
	public void UpdateGroup(int group_id,String group_name){
		ResultSet rs=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String sql="UPDATE USER_DEFINED_GROUPS SET GROUP_NAME='"+group_name+"' WHERE GROUP_ID="+group_id;	
			rs=ConStatement.executeQuery(sql);
		}catch(Exception ex){ex.getLocalizedMessage();}
	}
	
	public void InsertGroup(String group_name,int user_id){
		ResultSet rstgroup=null;
		ResultSet rstmember=null;
		try{
			Statement stmgroup=DBConnection.createStatement();
			String sqlgroup="INSERT INTO USER_DEFINED_GROUPS " +
					"(GROUP_ID," +
					"GROUP_NAME," +
					"GROUP_OWNER," +
					"DATE_CREATE)" +
					" VALUES" +
					"(GROUP_ID.nextVal," +
					"'"+group_name+"'," +
					""+user_id+"," +
					"SYSDATE)";
			rstgroup=stmgroup.executeQuery(sqlgroup);
			
			
			int group_id=this.getGroupIdByName(group_name);
			Statement stmmember=DBConnection.createStatement();
			String sqlmember="INSERT INTO GROUP_MEMBERS (GROUP_ID,USER_ID) VALUES ("+group_id+","+user_id+")";
			rstmember=stmmember.executeQuery(sqlmember);
		}catch(Exception ex){ex.getLocalizedMessage();}
	}
	
	public int getGroupIdByName(String group_name){
		ResultSet rst=null;
		int result=0;
		try{
			Statement stm=DBConnection.createStatement();
			String sql="SELECT GROUP_ID,GROUP_NAME FROM USER_DEFINED_GROUPS WHERE GROUP_NAME='"+group_name+"'";
			rst=stm.executeQuery(sql);
			while(rst.next()){
				result=rst.getInt("GROUP_ID");
			}
		}catch(Exception ex){
			ex.getLocalizedMessage();
		}
		return result;
	}
}